package br.com.velp.vskygeo.daoimpl;

import br.com.velp.vskygeo.comum.SpringConstants;
import br.com.velp.vskygeo.dao.MysqlDAO;
import br.com.velp.vskygeo.dto.*;
import br.com.velp.vskygeo.generic.HibernateTemplateMysqlDAO;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;
import org.hibernate.type.*;
import org.springframework.context.annotation.Lazy;
import org.springframework.stereotype.Repository;

/**
 * <sumario>
 * <objetivo>"Rastreabilidade"</objetivo>
 * <autor>EDUARDO SANTOS DE OLIVEIRA</autor>
 * <versao>1.0.0.0</versao>
 * <data_criacao> 06.08.2013 </data_criacao>
 * <data_ultima_alteracao> N/A </data_ultima_alteracao>
 * <descricao> Serviço Responsável Pelas Operações 
 *             Inerentes ao Banco de Dados MySQL
 * </descricao>
 * </sumario>
 */
@Repository()
@Lazy(SpringConstants.LAZY_BEANS)
public class MysqlDAOImpl extends HibernateTemplateMysqlDAO<Object> implements MysqlDAO {
    
    private static final Integer FUNCAO_MONITOR_SISTEMA = 7;
    
    @Override
    public List<UltimoDadoLidoResultadoConsultaDTO> recuperarUltimosDadosLidos(UsuarioLogadoDTO usuarioLogado) {
        
        StringBuilder consulta = new StringBuilder();
        
        consulta.append("SELECT UDL.ID_MONIT AS idMonitorado, ");
        consulta.append("       UDL.HORA_ULTIMA_POS AS horaUltimaPosicao, ");
        consulta.append("       UDL.VELOCIDADE AS velocidade, ");
        consulta.append("       UDL.LATITUDE AS latitude, ");
        consulta.append("       UDL.LONGITUDE AS longitude, ");
        consulta.append("       UDL.HODOMETRO AS hodometro, ");
        consulta.append("       UDL.HORIMETRO AS horimetro, ");
        consulta.append("       UDL.GPS_LIGADO AS gpsLigado, ");
        consulta.append("       UDL.TIPO_TRANSMISSAO AS tipoTransmissao, ");
        consulta.append("       UDL.IGNICAO_LIGADA AS ignicaoLigada, ");
        consulta.append("       UDL.WIFI_CONECTADO AS wifiConectado, ");
        consulta.append("       UDL.ULTIMA_HORA_WIFI AS horaUltimaTransmissaoWifi, ");
        consulta.append("       MNT.ID_CLIENTE AS idCliente, ");
        consulta.append("       MNT.DESC_MONITORADO AS descricaoMonitorado, ");
        consulta.append("       MNT.SERIAL_EQUIP AS serialEquipamento, ");
        consulta.append("       MNT.TIPO AS tipoEquipamento, ");
        consulta.append("       MNT.STATUS AS statusMonitorado ");
        consulta.append("FROM VW_MONITORADO MNT ");
        consulta.append("LEFT JOIN VW_ULTIMO_DADO_LIDO UDL ON (MNT.ID_MONIT = UDL.ID_MONIT) ");        
        
        if (!FUNCAO_MONITOR_SISTEMA.equals(usuarioLogado.getFuncao())) {
            consulta.append("WHERE MNT.ID_CLIENTE = :idCliente ");        
        }
        
        consulta.append("ORDER BY MNT.STATUS, MNT.DESC_MONITORADO ");        
        
        SQLQuery query = getSession().createSQLQuery(consulta.toString());
        query.setResultTransformer(Transformers.aliasToBean(UltimoDadoLidoResultadoConsultaDTO.class));
        
        if (!FUNCAO_MONITOR_SISTEMA.equals(usuarioLogado.getFuncao())) {
            query.setParameter("idCliente", usuarioLogado.getIdCliente());
        }
        
        query.addScalar("idMonitorado", IntegerType.INSTANCE);
        query.addScalar("idCliente", IntegerType.INSTANCE);
        query.addScalar("velocidade", IntegerType.INSTANCE);
        query.addScalar("hodometro", IntegerType.INSTANCE);
        query.addScalar("horimetro", IntegerType.INSTANCE);
        query.addScalar("descricaoMonitorado", StringType.INSTANCE);
        query.addScalar("serialEquipamento", StringType.INSTANCE);
        query.addScalar("gpsLigado", ShortType.INSTANCE);
        query.addScalar("tipoTransmissao", ShortType.INSTANCE);
        query.addScalar("ignicaoLigada", ShortType.INSTANCE);
        query.addScalar("wifiConectado", ShortType.INSTANCE);
        query.addScalar("tipoEquipamento", ShortType.INSTANCE);
        query.addScalar("statusMonitorado", ShortType.INSTANCE);
        query.addScalar("latitude", DoubleType.INSTANCE);
        query.addScalar("longitude", DoubleType.INSTANCE);
        query.addScalar("horaUltimaPosicao", TimestampType.INSTANCE);
        query.addScalar("horaUltimaTransmissaoWifi", TimestampType.INSTANCE);
        
        return query.list();
        
    }
    
    @Override
    public List<OcorrenciaResultadoConsultaDTO> recuperarOcorrencias(UsuarioLogadoDTO usuarioLogado) {
        
        StringBuilder consulta = new StringBuilder();
        
        consulta.append("SELECT OCORR.ID_OCORRENCIA AS idOcorrencia, ");
        consulta.append("OCORR.ID_MONITORADO AS idMonitorado, ");
        consulta.append("MNT.DESC_MONITORADO AS descricaoMonitorado, ");
        consulta.append("MNT.SERIAL_EQUIP AS serialEquipamento, ");
        consulta.append("MNT.STATUS AS statusMonitorado, ");
        consulta.append("UDL.LATITUDE AS latitudeMonitorado, ");
        consulta.append("UDL.LONGITUDE AS longitudeMonitorado, ");
        consulta.append("OCORR.NUMERO_LOGRADOURO AS numeroLogradouro, ");
        consulta.append("OCORR.NUM_OCORRENCIA AS numeroOcorrencia, ");
        consulta.append("OCORR.DATA_CRIACAO AS dataCriacao, ");
        consulta.append("OCORR.DESC_TIPO_ATEND AS descricaoTipoAtendimento, ");
        consulta.append("OCORR.MOTIVO AS motivo, ");
        consulta.append("OCORR.OBSERVACAO AS observacao, ");
        consulta.append("OCORR.PACIENTE AS paciente, ");
        consulta.append("OCORR.LOGRADOURO AS logradouro, ");
        consulta.append("OCORR.LOGRADOURO_REFERENCIA AS logradouroReferencia, ");
        consulta.append("OCORR.BAIRRO AS bairro, ");
        consulta.append("OCORR.TELEFONE_SOLICITANTE AS telefoneSolicitante, ");
        consulta.append("OCORR.CIDADE AS cidade, ");
        consulta.append("OCORR.UF AS uf, ");
        consulta.append("OCORR.SEXO AS sexo, ");
        consulta.append("OCORR.CODIGO_OCORRENCIA AS codigoOcorrencia ");
        consulta.append("FROM VW_OCORRENCIAS OCORR ");
        consulta.append("INNER JOIN VW_ULTIMO_DADO_LIDO UDL ON (OCORR.ID_MONITORADO = UDL.ID_MONIT) ");
        consulta.append("INNER JOIN VW_MONITORADO MNT ON (UDL.ID_MONIT = MNT.ID_MONIT) ");
        
        if (!FUNCAO_MONITOR_SISTEMA.equals(usuarioLogado.getFuncao())) {
            consulta.append("WHERE MNT.ID_CLIENTE = :idCliente ");
        }
        
        consulta.append("ORDER BY OCORR.DATA_CRIACAO DESC, MNT.STATUS ASC, NUM_OCORRENCIA DESC ");
        
        SQLQuery query = getSession().createSQLQuery(consulta.toString());
        query.setResultTransformer(Transformers.aliasToBean(OcorrenciaResultadoConsultaDTO.class));
        
        if (!FUNCAO_MONITOR_SISTEMA.equals(usuarioLogado.getFuncao())) {
            query.setParameter("idCliente", usuarioLogado.getIdCliente());
        }
        
        query.addScalar("idOcorrencia", IntegerType.INSTANCE);
        query.addScalar("idMonitorado", IntegerType.INSTANCE);
        query.addScalar("numeroLogradouro", IntegerType.INSTANCE);
        query.addScalar("statusMonitorado", ShortType.INSTANCE);
        query.addScalar("latitudeMonitorado", DoubleType.INSTANCE);
        query.addScalar("longitudeMonitorado", DoubleType.INSTANCE);
        query.addScalar("descricaoMonitorado", StringType.INSTANCE);
        query.addScalar("serialEquipamento", StringType.INSTANCE);
        query.addScalar("numeroOcorrencia", StringType.INSTANCE);
        query.addScalar("dataCriacao", TimestampType.INSTANCE);
        query.addScalar("descricaoTipoAtendimento", StringType.INSTANCE);
        query.addScalar("motivo", StringType.INSTANCE);
        query.addScalar("observacao", StringType.INSTANCE);
        query.addScalar("paciente", StringType.INSTANCE);
        query.addScalar("logradouro", StringType.INSTANCE);
        query.addScalar("logradouroReferencia", StringType.INSTANCE);
        query.addScalar("bairro", StringType.INSTANCE);
        query.addScalar("telefoneSolicitante", StringType.INSTANCE);
        query.addScalar("cidade", StringType.INSTANCE);
        query.addScalar("uf", StringType.INSTANCE);
        query.addScalar("sexo", StringType.INSTANCE);
        query.addScalar("codigoOcorrencia", StringType.INSTANCE);
        
        return query.list();
        
    }
    
    @Override
    public List<PontoInteresseResultadoConsultaDTO> obterPontosInteresse(UsuarioLogadoDTO usuarioLogado) {
        
        StringBuilder consulta = new StringBuilder();
        
        consulta.append("SELECT DESCRICAO AS descricao, ");
        consulta.append("LATITUDE AS latitude, ");
        consulta.append("LONGITUDE AS longitude, ");
        consulta.append("ID_CLASSIFICACAO AS idClassificacao, ");
        consulta.append("ID_CLIENTE AS idCliente ");
        consulta.append("FROM VW_PONTOS_INTERESSE ");
        
        if (!FUNCAO_MONITOR_SISTEMA.equals(usuarioLogado.getFuncao())) {
            consulta.append("WHERE ID_CLIENTE = :idCliente ");
        }
        
        consulta.append("ORDER BY DESCRICAO ");
        
        SQLQuery query = getSession().createSQLQuery(consulta.toString());
        query.setResultTransformer(Transformers.aliasToBean(PontoInteresseResultadoConsultaDTO.class));
        
        if (!FUNCAO_MONITOR_SISTEMA.equals(usuarioLogado.getFuncao())) {
            query.setParameter("idCliente", usuarioLogado.getIdCliente());
        }
        
        query.addScalar("descricao", StringType.INSTANCE);
        query.addScalar("latitude", DoubleType.INSTANCE);
        query.addScalar("longitude", DoubleType.INSTANCE);
        query.addScalar("idClassificacao", IntegerType.INSTANCE);
        query.addScalar("idCliente", IntegerType.INSTANCE);
        
        return query.list();
        
    }
    
    @Override
    public List<HistoricoDadoLidoDTO> obterHistoricoDadoLido(Integer idMonitorado, Date dataInicial, Date dataFinal) {
        
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        
        StringBuilder consulta = new StringBuilder();
        
        consulta.append("SELECT ID_MONITORADO AS idMonitorado, ");
        consulta.append("       DATA AS data, ");
        consulta.append("       LATITUDE AS latitude, "); 
        consulta.append("       LONGITUDE AS longitude "); 
        consulta.append("FROM VW_HIST_DADO_LIDO ");        
        consulta.append("WHERE ID_MONITORADO = :idMonitorado ");        
        consulta.append("AND DATA BETWEEN :dataInicial AND :dataFinal ");        
        consulta.append("ORDER BY DATA ASC ");        
        
        SQLQuery query = getSession().createSQLQuery(consulta.toString());
        query.setResultTransformer(Transformers.aliasToBean(HistoricoDadoLidoDTO.class));
        
        query.setParameter("idMonitorado", idMonitorado);
        query.setParameter("dataInicial", sdf.format(dataInicial));
        query.setParameter("dataFinal", sdf.format(dataFinal));
        
        query.addScalar("idMonitorado", IntegerType.INSTANCE);
        query.addScalar("data", TimestampType.INSTANCE);
        query.addScalar("latitude", DoubleType.INSTANCE);
        query.addScalar("longitude", DoubleType.INSTANCE);
        
        return query.list();
        
    }
    
}
